package cn.edu.nju.ws.sview.database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashSet;

import cn.edu.nju.ws.sview.cache.URIIDCache;
import cn.edu.nju.ws.sview.crawler.Fetch;
import cn.edu.nju.ws.sview.rdf.URIResource;
import cn.edu.nju.ws.sview.util.MD5;
import cn.edu.nju.ws.sview.util.PropertyList;

import com.hp.hpl.jena.vocabulary.RDF;

/**
 * 
 * @author gongsaisai
 *
 */
public class DBOperations {
	
	public static String getURI(int uriID) throws Throwable {
		String uri = null;

		Connection conn = DBConnectionFactory.getConnection();
		PreparedStatement stmt = conn
				.prepareStatement("SELECT uri FROM uri WHERE id=?");
		stmt.clearParameters();
		stmt.setInt(1, uriID);
		ResultSet rs = stmt.executeQuery();
		if (rs.next()) {
			uri = rs.getString("uri");
		}
		stmt.close();
		conn.close();

		return uri;
	}

	/**
	 * 
	 * @param uri
	 * @param insert. If true insert a new uri and get the id
	 * @return 0 if not exists
	 * @throws Throwable
	 */
	public static int getURIID(String uri, boolean insert) throws Throwable {
		int uriID = 0;

		while (uriID == 0) {
			Connection conn = DBConnectionFactory.getConnection();
			PreparedStatement stmt = conn
					.prepareStatement("SELECT id FROM uri WHERE uri=?");
			stmt.clearParameters();
			stmt.setString(1, uri);
			ResultSet rs = stmt.executeQuery();
			if (rs.next()) {
				uriID = rs.getInt("id");
			}
			rs.close();
			stmt.close();
			conn.close();

			if (!insert) {
				break;
			}

			if (uriID != 0) {
				break;
			}

			MD5 md5 = new MD5();
			String sURI = uri.substring(0, Math.min(uri.length(),
					DBConnectionFactory.STRING_MAX_LENGTH));
			md5.addString(sURI);
			byte[] hash = md5.getResult();
			md5.close();

			conn = DBConnectionFactory.getConnection();
			stmt = conn
					.prepareStatement("INSERT INTO uri(uri,md5) VALUES(?,?) ON DUPLICATE KEY UPDATE uri=VALUES(uri)");
			stmt.clearParameters();
			stmt.setString(1, sURI);
			stmt.setBytes(2, hash);
			stmt.executeUpdate();
			stmt.close();
			conn.close();
		}

		return uriID;
	}

	/**
	 * get dereference doc id
	 * @param uri
	 * @return 0 if not dereferenced or has no dereference doc
	 * @throws Throwable
	 */
	public static int getDereferenceDocID(String uri) throws Throwable {
		if (uri == null) {
			return 0;
		}

		int hashPos = uri.indexOf('#');
		if (hashPos != -1) {
			uri = uri.substring(0, hashPos);
		}

		int docID = 0;

		Connection conn = DBConnectionFactory.getConnection();
		PreparedStatement stmt = conn
				.prepareStatement("SELECT deref FROM uri WHERE uri=?");
		stmt.clearParameters();
		stmt.setString(1, uri);
		ResultSet rs = stmt.executeQuery();
		if (rs.next()) {
			docID = rs.getInt("deref");
		}
		rs.close();
		stmt.close();
		conn.close();

		return docID;
	}
	
	/**
	 * get the type of the uri
	 * @param uri
	 * @return empty if not find related statement in dereference doc
	 * @throws Throwable
	 */
	public static HashSet<Integer> getTypes(String uri) throws Throwable{
		HashSet<Integer> types = new HashSet<Integer>();
		if (uri == null) {
			return types;
		}

		int s = getURIID(uri,false);
		int p = getURIID(RDF.type.getURI(),true);
		int doc_id = getDereferenceDocID(uri);
		Connection conn = DBConnectionFactory.getConnection();
		PreparedStatement stmt = conn
				.prepareStatement("SELECT o FROM quadruple WHERE s=? and p=? and doc_id=? and o like 'u%'");
		stmt.setString(1, "u" + s);
		stmt.setString(2, "u" + p);
		stmt.setInt(3, doc_id);
		ResultSet rs = stmt.executeQuery();
		while (rs.next()) {
			int typeId = Integer.parseInt(rs.getString("o").substring(1));
			types.add(new Integer(typeId));
		}
		rs.close();
		stmt.close();
		conn.close();
		
		return types;
	}
	
	/**
	 * get the optimal label of the uri of the specific lang
	 * If no lang is specified ,belong to english default
	 * @param uri
	 * @return empty if no label of the lang found
	 * @throws Throwable
	 */
	public static String getLabel(String uri,String lang) throws Throwable{
		String label = "";
		if (uri == null) {
			return label;
		}

		ArrayList<String> properties = PropertyList.getLabelProperties();
		ArrayList<String> array = new ArrayList<String>();
		for(String str:properties){
			int p = URIIDCache.getInstance().getURIID(str, true) ;
			array.add("u"+p) ;
		}
	
		int s =  URIIDCache.getInstance().getURIID(uri, true) ;
		int doc_id = getDereferenceDocID(uri);
		Connection conn = DBConnectionFactory.getConnection();
		PreparedStatement stmt = null;
		if(lang==null){
			stmt = conn
			 .prepareStatement("SELECT o FROM quadruple WHERE s=? and doc_id=? and p=?  and o like 'l%'");
            stmt.setString(1, "u"+s) ;
			stmt.setInt(2, doc_id);
		}else if(lang.equals("en")){
			stmt = conn
			.prepareStatement("SELECT o FROM quadruple WHERE s=? and doc_id=? and p=?  and ((lang like 'en%') or (lang='' )) and o like 'l%'");
            stmt.setString(1, "u"+s) ;
			stmt.setInt(2, doc_id);
		}else{
			stmt = conn
			.prepareStatement("SELECT o FROM quadruple WHERE s=? and doc_id=? and p=?  and lang like ? and o like 'l%'");
		    stmt.setString(1, "u"+s) ;
			stmt.setInt(2, doc_id);
			stmt.setString(4, lang+"%") ;
		}
		
		for(String property:array){
			stmt.setString(3, property) ;
			ResultSet rs = stmt.executeQuery();
			if(rs.next()){
				label = rs.getString("o").substring(1);
				break;
			}
			rs.close();
		}
		stmt.close();
		conn.close();
		
		return label;
	}
	
	public static String getLabelOfBlankNode(int documentID,int blankNodeID,String lang) throws Throwable{
		String label = "" ;
		ArrayList<String> properties = PropertyList.getLabelProperties();
		ArrayList<String> array = new ArrayList<String>();
		for(String str:properties){
			int p = URIIDCache.getInstance().getURIID(str, true) ;
			array.add("u"+p) ;
		}
		
		String s = "b"+documentID+":"+blankNodeID;
		int doc_id = documentID;
		Connection conn = DBConnectionFactory.getConnection();
		PreparedStatement stmt = null;
		if(lang==null){
			stmt = conn
			 .prepareStatement("SELECT o FROM quadruple WHERE s=? and doc_id=? and p=?  and o like 'l%'");
            stmt.setString(1, s) ;
			stmt.setInt(2, doc_id);
		}else if(lang.equals("en")){
			stmt = conn
			.prepareStatement("SELECT o FROM quadruple WHERE s=? and doc_id=? and p=?  and ((lang like 'en%') or (lang='' )) and o like 'l%'");
            stmt.setString(1, s) ;
			stmt.setInt(2, doc_id);
		}else{
			stmt = conn
			.prepareStatement("SELECT o FROM quadruple WHERE s=? and doc_id=? and p=?  and lang=? and o like 'l%'");
		    stmt.setString(1, s) ;
			stmt.setInt(2, doc_id);
			stmt.setString(4, lang) ;
		}
		
		for(String property:array){
			stmt.setString(3, property) ;
			ResultSet rs = stmt.executeQuery();
			if(rs.next()){
				label = rs.getString("o").substring(1);
				break;
			}
			rs.close();
		}
		stmt.close();
		conn.close();
		
		return label;
	}
	
	 /**
	  * check whether the uri has been pinged or expired
	  * @param uri
	  * @return
	  */
	 public static String getValidStatus(int uriID){
		 String result = null;
		 Connection con = DBConnectionFactory.getConnection();
		 int uid = 0;
		 try {
			String uri = Fetch.removeFragment(URIIDCache.getInstance().getURI(uriID));
			uid = URIIDCache.getInstance().getURIID(uri, true) ;
		} catch (Throwable e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		 try {
			 PreparedStatement stmt = con.prepareStatement("select lastping,expire from uri where id=?") ;
			 stmt.setInt(1, uid) ;
			 ResultSet rs  = stmt.executeQuery();
			 if(rs.next()){
				 long lastping = rs.getLong("lastping") ;
				 long expire = rs.getLong("expire") ;
				 if(lastping==0)
					 result = URIResource.NOT_PINGED;
				 else{
					 long now = (new Date()).getTime();
					 if(now>=expire)
						 result = URIResource.EXPIRED;
					 else{
						 result = URIResource.VALID;
					 }
				 }
			 }
			 rs.close();
			 con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return result;
	 }
	 
	 /**
	  * check whether the uri has been pinged and success
	  * @param uri
	  * @return
	  */
	 public static String getSuccessStatus(int uriID){
		 String result = URIResource.NOT_PINGED;
		 Connection con = DBConnectionFactory.getConnection();
		 int uid = 0;
		 try {
			String uri = Fetch.removeFragment(URIIDCache.getInstance().getURI(uriID));
			if(uri!=null)
				uid = URIIDCache.getInstance().getURIID(uri, true) ;
		} catch (Throwable e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		 try {
			 PreparedStatement stmt = con.prepareStatement("select deref,lastping,expire from uri where id=?") ;
			 stmt.setInt(1, uid) ;
			 ResultSet rs  = stmt.executeQuery();
			 if(rs.next()){
				 long lastping = rs.getLong("lastping") ;
				 long expire = rs.getLong("expire") ;
				 int deref = rs.getInt("deref") ;
				 if(lastping==0)
					 result = URIResource.NOT_PINGED;
				 else{
					 long now = (new Date()).getTime();
					 if(now>=expire)
						 result = URIResource.EXPIRED;
					 else if(deref==0){
						 result = URIResource.VALID_FAILED;
					 }else
						 result = URIResource.VALID_SUCCEED;
				 }
			 }
			 rs.close();
			 con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return result;
	 }
}
